package com.housekeeping.housekeeping.advertisingmanagement.dao.impl;/*
 * @author  pyh
 * @date  2021/1/22 0022 上午 9:30
 * @version 1.0
 * @since JDK1.8_241
 */

import com.housekeeping.housekeeping.advertisingmanagement.dao.SearchDao;
import com.housekeeping.housekeeping.advertisingmanagement.entity.AdMessages;
import com.housekeeping.housekeeping.order.entity.Order;
import com.housekeeping.housekeeping.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SearchDaoImpl implements SearchDao {
    //通过状态值查询
    @Override
    public List<AdMessages> getStatusList(int status, int page, int size) throws SQLException {
        ArrayList<AdMessages> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        AdMessages adMessages = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select * from advertise where status = ? limit ?,?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            pStmt.setInt(2, page);
            pStmt.setInt(3, size);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                adMessages = new AdMessages();
                adMessages.setId(rs.getInt(1));
                adMessages.setStatus(rs.getInt(2));
                adMessages.setName(rs.getString(3));
                adMessages.setAssociateditems(rs.getString(4));
                adMessages.setStarttime(rs.getDate(5));
                adMessages.setEndtime(rs.getDate(6));
                adMessages.setVisitors(rs.getInt(7));
                adMessages.setCreatetime(rs.getDate(8));
                list.add(adMessages);
            }
        }finally {
            DBManager.close(rs, pStmt, conn);
        }
        return list;
    }
    //获取对应状态值的广告条数
    @Override
    public int getStatusCount(int status) throws SQLException {
        Connection conn = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from advertise where status = ?";
            conn = DBManager.getConnection();
            pStmt = conn.prepareStatement(sql);
            pStmt.setInt(1, status);
            rs = pStmt.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        }finally {
            DBManager.close(rs, pStmt, conn);
        }
    }

    //通过状态值和广告名称查询
    @Override
    public List<AdMessages> getNameList(int status,String name,  int page, int size) {
        ArrayList<AdMessages> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        AdMessages adMessages = null;
        try {
            conn =DBManager.getConnection();
            String sql = "select * from advertise where status = ? and name = ?  limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,status);
            ps.setString(2,name);
            ps.setInt(3,page);
            ps.setInt(4,size);
            rs =ps.executeQuery();
            while (rs.next()){
                adMessages = new AdMessages();
                adMessages.setId(rs.getInt(1));
                adMessages.setStatus(rs.getInt(2));
                adMessages.setName(rs.getString(3));
                adMessages.setName(rs.getString(4));
                adMessages.setStarttime(rs.getDate(5));
                adMessages.setEndtime(rs.getDate(6));
                adMessages.setVisitors(rs.getInt(7));
                adMessages.setCreatetime(rs.getDate(8));
                list.add(adMessages);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBManager.close(rs,ps,conn);
        }
        return list;
    }

    @Override
    public int getCount(int status, String name) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int totalCount = 0;
        try {
            conn = DBManager.getConnection();
            String sql = "select count(*) from advertise where status = ? and name = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, status);
            ps.setString(2, name);
            rs = ps.executeQuery();
            totalCount = rs.next() ? rs.getInt(1) : 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.close(rs, ps, conn);
        }
        return totalCount;
    }
}
